package com.presalept.manipulatedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import com.presalept.polodo.ProjectDO;
import com.presalept.polodo.ProjectMemberDO;


public class UpdateProject {
	
	public static Integer UpdataProjectData(ProjectDO pdo) throws SQLException{
		
		Integer UpdateResult = 0;
		Connection dbConn = null;
		PreparedStatement dbUpdateProjectStmt = null;
		PreparedStatement dbInsertProjectMemberStmt = null;
		PreparedStatement dbDeleteProjectMemberStmt = null;
		ResultSet dbRs = null;
		int[] insertProjectMember = null;
		int[] deleteProjectMember = null;
		Integer updateProject = 0;
	
		//create update projet sql
		
		String sqlProject = "UPDATE Project SET";
		boolean NeedUpdate = false;
		
		if(pdo.getProjectName() != null){
			sqlProject = sqlProject + " projectname = " + pdo.getProjectName();
			NeedUpdate = true;
		}
		if(pdo.getProjectDescription() != null){
			sqlProject = sqlProject + " projectdescription = " + pdo.getProjectDescription();
			NeedUpdate = true;
		}
		if(pdo.getClientidofproject() != 0){
			sqlProject = sqlProject + " clientidofproject = " + pdo.getClientidofproject();
			NeedUpdate = true;
		}
		if(NeedUpdate){
			sqlProject = sqlProject + " where projectid = " + pdo.getProjectID();
		}

				
		try{
			
				dbConn = DbManager.getConnection();	
			
				dbConn.setAutoCommit(false);
			
				//update project 
			
				if(NeedUpdate){
			
					dbUpdateProjectStmt = dbConn.prepareStatement(sqlProject);
				
					updateProject = dbUpdateProjectStmt.executeUpdate();
			
				}
			
				if(updateProject > 0)
				{
					//insert projectmember
					
					boolean isRollBack = false;
					
					if(pdo.getProjectMember() != null){
						
						
						String sqlProjectMember = "INSERT INTO relationofprojectanduser (projectid,userid,purelationstatus,createdate)"
								+ " VALUES(?,?,?,?); ";
						
						dbInsertProjectMemberStmt = dbConn.prepareStatement(sqlProjectMember);
						
						for(Iterator<ProjectMemberDO> i = pdo.getProjectMember().iterator(); i.hasNext();){
							
							ProjectMemberDO pmdo = i.next();
							
							//when OprateType == 1 then insert it
						
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");  
						    Date createdate = null;  
					        try {  
					        	
					        	createdate = sdf.parse(pmdo.getCreatedate()); 
				  
					        } catch (ParseException e) {  
					            e.printStackTrace();  
					        }       
						    java.sql.Date sqlcreateDate = new java.sql.Date(createdate.getTime()); 
								
						    dbInsertProjectMemberStmt.setInt(1, pmdo.getProjectId());
						    dbInsertProjectMemberStmt.setInt(2, pmdo.getUserId());
						    dbInsertProjectMemberStmt.setInt(3, pmdo.getPurelationstatus());
						    dbInsertProjectMemberStmt.setDate(4, sqlcreateDate);
								
						    dbInsertProjectMemberStmt.addBatch();		
						}
	
						insertProjectMember = dbInsertProjectMemberStmt.executeBatch();
						
						//check insert results
					
						for(int j = 0; j < insertProjectMember.length; j++){
								
							//if some record has insert error, then rollback
								
							if(insertProjectMember[j] == 0){
								dbConn.rollback();
								isRollBack = true;
								break;
							}		
	
						}
					}
					if(!isRollBack){
						if(pdo.getProjectMemberDel() != null){
							
							String sqlProjectMemberDel = "DELETE FROM relationofprojectanduser WHERE projectid =? AND userid = ?;";
							
							dbDeleteProjectMemberStmt = dbConn.prepareStatement(sqlProjectMemberDel);
							
							for(Iterator<ProjectMemberDO> i = pdo.getProjectMemberDel().iterator(); i.hasNext();){
								
								ProjectMemberDO pmdodel = i.next();
								
								//when OperateType == 0 delete the record
								
								if(pmdodel.getOperateType() == 0){
									
									dbDeleteProjectMemberStmt.setInt(1, pmdodel.getProjectId());
								
									dbDeleteProjectMemberStmt.setInt(2, pmdodel.getUserId());
								
									dbDeleteProjectMemberStmt.addBatch();	
								}
							}
						
		
							deleteProjectMember = dbDeleteProjectMemberStmt.executeBatch();
							
							//check insert results
						
							for(int j = 0; j < deleteProjectMember.length; j++){
									
								//if some record has insert error, then rollback
									
								if(deleteProjectMember[j] == 0){
									dbConn.rollback();
									isRollBack = false;
									break;
								}		
							}							
						}
					}
					if(!isRollBack){
						
						//commit data
						
						dbConn.commit();
						UpdateResult = 1;
					}
				}
				else{
						dbConn.rollback();
				}		
			}finally{
				if (dbRs != null) dbRs.close();
				if (dbConn != null) dbConn.close();			
			}	
		
		return UpdateResult;
	}

}
